<?php
	class UserConverter{
		public static function convert($name, $surname, $login, $password, $phone, $address, $city, $id, $role){
			$result = new User();
			$result->setName($name);
			$result->setSurname($surname);
			$result->setLogin($login);
			$result->setPassword(base64_decode($password));
			$result->setPhone($phone);
			$result->setAddress($address);
			$result->setCity($city);
			$result->setId($id);
			$result->setRole($role);
			return $result;
		}
	}	

	class UserLoginCommand implements Command{
		private $login;
		private $password;
		public function __construct($login, $password){
			$this->login = $login;
			$this->password = $password;
		}
		
		public function doExecute($connection){

			$this->login = VariableUtils::getRealEscapeString($this->login);
			$this->password = VariableUtils::getRealEscapeString($this->password);
			$query = "select " . BoaConstans::USER_COLUMN_ID . ", " . 
			BoaConstans::USER_COLUMN_ROLE_ID . ", " . 
			BoaConstans::USER_COLUMN_NAME  . ", " .
			BoaConstans::USER_COLUMN_SURNAME . ", " .
			BoaConstans::USER_COLUMN_ADDRESS . ", " .
			BoaConstans::USER_COLUMN_PHONE . ", " .
			BoaConstans::USER_COLUMN_CITY .
			" from " . BoaConstans::USER_TABLE . " where " .
			BoaConstans::USER_COLUMN_LOGIN . "= ? and " .
			BoaConstans::USER_COLUMN_PASSWORD . "= ?";
			
			$stmt = $connection->prepare($query);
			$stmt->bind_param("ss", $this->login, base64_encode($this->password));
			$stmt->execute();
			
			$id = null;
			$rid = null;
			$n = null;
			$s = null;
			$a = null;
			$p = null;
			$c = null;
			$stmt->bind_result($id, $rid, $n, $s, $a, $p, $c);
			$stmt->fetch();
			$stmt->close();
			$result = null;
			if(VariableUtils::isNumber($id)){
				$result = UserConverter::convert($n, $s, $this->login, $this->password, $p, $a, $c, $id, $rid);
			}
			return $result;
		}
	}

	class UserFindCommand implements Command{
		private $controls;
		public function __construct(UserSearchControls $controls){
			$this->controls = $controls;
		}
		public function doExecute($connection){
			$result = array();
						
			$query = "select ut." . BoaConstans::USER_COLUMN_ID . ", ut." . 
			BoaConstans::USER_COLUMN_ROLE_ID . ", ut." . 
			BoaConstans::USER_COLUMN_NAME  . ", ut." .
			BoaConstans::USER_COLUMN_SURNAME . ", ut." .
			BoaConstans::USER_COLUMN_ADDRESS . ", ut." .
			BoaConstans::USER_COLUMN_PHONE . ", ut." .
			BoaConstans::USER_COLUMN_CITY . ", ut." .
			BoaConstans::USER_COLUMN_LOGIN . ", ut." .
			BoaConstans::USER_COLUMN_PASSWORD .
			" from " . BoaConstans::USER_TABLE . " ut ";
			
			$where = null;
			$types = "";
			$params = array();
			
			
			if(VariableUtils::isBoolean($this->controls->isService())){
				$serviceQuery  = null;
				if($this->controls->isService()){
					$serviceQuery = "ut.".BoaConstans::USER_COLUMN_ID." in(select ust.". BoaConstans::USER_SERVICE_COLUMN_UID. " from " . BoaConstans::USER_SERVICE_TABLE . " ust)";
				}else{
					$serviceQuery = "ut.".BoaConstans::USER_COLUMN_ID." not in(select ust.". BoaConstans::USER_SERVICE_COLUMN_UID. " from " . BoaConstans::USER_SERVICE_TABLE . " ust)";
				} 
				$this->addToWhere($where, $serviceQuery);
			}
			

			if(VariableUtils::isBoolean($this->controls->isTicket()) && true == $this->controls->isService()){
				if($this->controls->isTicket()){
					$tickQuery = "ut.".BoaConstans::USER_COLUMN_ID." in(select ust.". BoaConstans::USER_SERVICE_COLUMN_UID. " from " . BoaConstans::USER_SERVICE_TABLE . " ust
					where ust." .  BoaConstans::USER_SERVICE_COLUMN_ID . " in(select tt." . BoaConstans::TICKET_COLUMN_USID . " from " . BoaConstans::TICKET_TABLE . " tt))";
				}else{
					$tickQuery = "(ut.".BoaConstans::USER_COLUMN_ID." in(select ust.". BoaConstans::USER_SERVICE_COLUMN_UID. " from " . BoaConstans::USER_SERVICE_TABLE . " ust
					where ust." .  BoaConstans::USER_SERVICE_COLUMN_ID . " not in(select tt." . BoaConstans::TICKET_COLUMN_USID . " from " . BoaConstans::TICKET_TABLE . " tt)) or ut." 
					.BoaConstans::USER_COLUMN_ID. " not in (select ust.". BoaConstans::USER_SERVICE_COLUMN_UID. " from " . BoaConstans::USER_SERVICE_TABLE . " ust))";
				}
				
				$this->addToWhere($where, $tickQuery);
			}
			
			
			
			
			if(VariableUtils::isBoolean($this->controls->isPaymentProblem()) && true == $this->controls->isService()){
				$paymentQuery = null;
				if($this->controls->isPaymentProblem()){
						$paymentQuery = "ut.".BoaConstans::USER_COLUMN_ID . " in(select ust." .BoaConstans::USER_SERVICE_COLUMN_UID  . " from " . BoaConstans::USER_SERVICE_TABLE . " ust where ust." .
						BoaConstans::USER_SERVICE_COLUMN_ID . " in (select pt." . BoaConstans::PAYMENT_COLUMN_USID . " from " . BoaConstans::PAYMENT_TABLE . " pt where pt." . 
						BoaConstans::PAYMENT_COLUMN_PAID_TILL_DATE . " < ? and pt." . BoaConstans::PAYMENT_COLUMN_IS_PAID . " = ?))";
						echo "payment jest";
				}else{
						$paymentQuery = "ut.".BoaConstans::USER_COLUMN_ID . " in(select ust." .BoaConstans::USER_SERVICE_COLUMN_UID  . " from " . BoaConstans::USER_SERVICE_TABLE . " ust where ust." .
						BoaConstans::USER_SERVICE_COLUMN_ID . " not in (select pt." . BoaConstans::PAYMENT_COLUMN_USID . " from " . BoaConstans::PAYMENT_TABLE . " pt where pt." . 
						BoaConstans::PAYMENT_COLUMN_PAID_TILL_DATE . " < ? and pt." . BoaConstans::PAYMENT_COLUMN_IS_PAID . " = ? ))";
						echo "payment nie ma";
				}
				$types .="ss";
				$params[] = @date("Y-m-d");
				$params[] = "false";
				$this->addToWhere($where, $paymentQuery);
			}
			
			
			
			if($this->controls->getName() != null){
				$types .="s";
				$params[] = VariableUtils::getRealEscapeString($this->controls->getName());
				$this->addToWhere($where, "upper(ut.".BoaConstans::USER_COLUMN_NAME .") like upper(CONCAT('%', ?, '%'))");
			}
			
			if($this->controls->getRole() != UserRoles::DEFAULT_ROLE && VariableUtils::isNumber($this->controls->getRole())){
				$types .="i";
				$params[] = $this->controls->getRole();
				$this->addToWhere($where,  "ut.".BoaConstans::USER_COLUMN_ROLE_ID ."=?");
			}
			
			if(VariableUtils::isNumber($this->controls->getId())){
				$types .="i";
				$params[] = $this->controls->getId();
				$this->addToWhere($where,  "ut.".BoaConstans::USER_COLUMN_ID ."=?");				
			}
		
			if($where != null){
				$query.=" where " . $where;				
			}
			
			$stmt = $connection->prepare($query);
			if($types != null && $types != ""){
				$bind_param = array();
				$bind_param[] = $types;
				for($i=0;$i<sizeof($params);$i++){
					$bind_param[] = &$params[$i];
				}
				call_user_func_array(array($stmt, 'bind_param'), $bind_param);
			}
			$stmt->execute();
			$id = null;
			$rid = null;
			$n = null;
			$s = null;
			$a = null;
			$p = null;
			$c = null;
			$l = null;
			$pss = null;
			$stmt->bind_result($id, $rid, $n, $s, $a, $p, $c, $l, $pss);
			while($stmt->fetch()){
				$result[] = UserConverter::convert($n, $s, $l, $pss, $p, $a, $c, $id, $rid);
			}
			return $result;
		}
		
		private function addToWhere(&$where, $condition){
			if($where == null){
					$where = $condition;
			}else{
				$where.= " and " . $condition;
			}
		}
	}
	
	class UserChangeRoleCommand implements Command{
		private $userId;
		private $userRole;
		
		public function __construct($userId, $userRole){
			$this->userId = $userId;
			$this->userRole = $userRole;			
		}
		
		public function doExecute($connection){
			
			if(!VariableUtils::isNumber($this->userId) || !VariableUtils::isNumber($this->userRole)){
				return false;
			}
			
			$query = "update " . BoaConstans::USER_TABLE . " set " .
			BoaConstans::USER_COLUMN_ROLE_ID. "=?" . " where " .
			BoaConstans::USER_COLUMN_ID. "=?";
			
			$stmt = $connection->prepare($query);
			$stmt->bind_param("ii", $this->userRole, $this->userId);
			$stmt->execute();
			$stmt->close();
			return true;
		}
	}
	
	class UserAddCommand implements Command{
		private $user;
		public function __construct(User $user){
			$this->user = $user;			
		}
		
		public function doExecute($connection){
			if($this->user == null){
				return false;
			}	
			
			$r = null;
			if(UserRoles::GUEST_ROLE == $this->user->getRole()){
				$r = UserRoles::USER_ROLE;
			}else if($this->user->getRole() == null){
				$r = UserRoles::USER_ROLE;
			}else {
				$r = $this->user->getRole();
			}
			$n = VariableUtils::getRealEscapeString($this->user->getName());
			$s = VariableUtils::getRealEscapeString($this->user->getSurname());
			$a = VariableUtils::getRealEscapeString($this->user->getAddress());
			$p = VariableUtils::getRealEscapeString($this->user->getPhone());
			$c = VariableUtils::getRealEscapeString($this->user->getCity());
			$l = VariableUtils::getRealEscapeString($this->user->getLogin());
			$pss = VariableUtils::getRealEscapeString($this->user->getPassword());
			
			$query = "insert into " . BoaConstans::USER_TABLE . " ( " .
			BoaConstans::USER_COLUMN_NAME  . ", ". 
			BoaConstans::USER_COLUMN_SURNAME . ", ". 
			BoaConstans::USER_COLUMN_ADDRESS . ", ". 
			BoaConstans::USER_COLUMN_PHONE . ", ". 
			BoaConstans::USER_COLUMN_CITY . ", ". 
			BoaConstans::USER_COLUMN_LOGIN . ", ". 
			BoaConstans::USER_COLUMN_PASSWORD . ", ". 
			BoaConstans::USER_COLUMN_ROLE_ID . ") values(?, ? , ? , ? , ?, ?, ? , ?) ";
			
			$stmt = $connection->prepare($query);
			$stmt->bind_param("sssssssi", $n, $s, $a, $p, $c, $l, base64_encode($pss), $r);
			$stmt->execute();
			$stmt->close();
			return mysqli_insert_id($connection);
		}
		
	}
	
	class UserDeleteCommand implements Command{
		private $id;
		public function __construct($id){
				$this->id = $id;
		}
		public function doExecute($connection){
			if($this->id == null || !VariableUtils::isNumber($this->id)){
				return false;
			}
			$query = "delete from " . BoaConstans::USER_TABLE . " where " . BoaConstans::USER_COLUMN_ID . "=?";
			$stmt = $connection->prepare($query);
			$stmt->bind_param("i", $this->id);
			$stmt->execute();
			$ar = $stmt->affected_rows;
			$stmt->close();
			return $ar > 0 ? true : false;
		}
	}
	
	class UserModifyCommand implements Command{
		private $user;
		public function __construct(User $user){
			$this->user = $user;
		}
		
		public function doExecute($connection){
			if($this->user == null){	
				return false;
			}
			
			if($this->user->getId() == null){
				// modyfikacja na uzytkowniku ktory nie posiada identyfikatora, traktowana jako dodawanie
				$uac = new UserAddCommand($this->user);
				return $uac->doExecute($connection);
			}
			
			$n = VariableUtils::getRealEscapeString($this->user->getName());
			$s = VariableUtils::getRealEscapeString($this->user->getSurname());
			$a = VariableUtils::getRealEscapeString($this->user->getAddress());
			$p = VariableUtils::getRealEscapeString($this->user->getPhone());
			$c = VariableUtils::getRealEscapeString($this->user->getCity());
			$l = VariableUtils::getRealEscapeString($this->user->getLogin());
			$pss = VariableUtils::getRealEscapeString($this->user->getPassword());
			
			$id = null;
			if(VariableUtils::isNumber($this->user->getId())){
				$id = $this->user->getId();
			}else{
				return false;
			}
			
			$r = null;
			if(VariableUtils::isNumber($this->user->getRole())){
				$r = $this->user->getRole();
			}else{
				return false;
			}
			
			$query = "update " . BoaConstans::USER_TABLE . " set " .
			BoaConstans::USER_COLUMN_NAME	  . "=?, " .
			BoaConstans::USER_COLUMN_SURNAME  . "=?, " .
			BoaConstans::USER_COLUMN_ADDRESS  . "=?, " .
			BoaConstans::USER_COLUMN_PHONE	  . "=?, " .
			BoaConstans::USER_COLUMN_CITY     . "=?, " .
			BoaConstans::USER_COLUMN_LOGIN    . "=?, " .			
			BoaConstans::USER_COLUMN_PASSWORD . "=?, " . 
			BoaConstans::USER_COLUMN_ROLE_ID  . "=? " .
			"where " . BoaConstans::USER_COLUMN_ID . "=?";			
			
			$stmt = $connection->prepare($query);
			$stmt->bind_param("sssssssii", $n, $s, $a, $p, $c, $l, base64_encode($pss), $r, $id);
			$stmt->execute();
			$ar = $stmt->affected_rows;
			$stmt->close();
			
			return $ar > 0 ? true : false;
		}
	}
	
	class UserGetCommand implements Command{
		
		private $id;
		public function __construct($id){
			$this->id =$id;
		}
		public function doExecute($connection){
			$query = "select ".
			BoaConstans::USER_COLUMN_NAME . ", ".
			BoaConstans::USER_COLUMN_SURNAME . ", ".
			BoaConstans::USER_COLUMN_ADDRESS . ", ".
			BoaConstans::USER_COLUMN_CITY . ", ".
			BoaConstans::USER_COLUMN_PHONE . ", ".
			BoaConstans::USER_COLUMN_LOGIN . ", ".
			BoaConstans::USER_COLUMN_PASSWORD . ", ".
			BoaConstans::USER_COLUMN_ROLE_ID . " from " .
			BoaConstans::USER_TABLE . " where " . BoaConstans::USER_COLUMN_ID. "=?";
			
				$stmt = $connection->prepare($query);
				$stmt->bind_param("i", $this->id);
				$stmt->execute();

				$name = null;
				$surname = null;
				$address = null;
				$city = null;
				$phone = null;
				$login = null;				
				$password = null;
				$role = null;			
				$stmt->bind_result($name, $surname, $address, $city, $phone, $login, $password, $role);
				$stmt->fetch();
				$result = UserConverter::convert($name, $surname, $login, $password, $phone, $address, $city, $this->id, $role);
				$stmt->close();
				
				return $result;				
		}
	}
?>